package business.statistics

import dao.Center

import dao.GroupedWord
import dao.Word
import groovy.sql.DataSet
import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import org.apache.commons.math.stat.descriptive.DescriptiveStatistics
import org.apache.log4j.Logger;
import business.threads.Monitor;
import business.coding.CenterType
import business.database.Database;
import business.parser.corpUtils;
import java.sql.ResultSet
import java.util.List;
import java.util.regex.Pattern;
import utils.*;

/**
 * Esta clase se encarga de generar las sentencias sql para calcular las estadisticas de grupos
 * y palaras, ejecutarlas y guardar los resultados en la base de datos
 * 
 * Tiene un solo metodo publico cuyos parametros deciden que estadisticas crear
 * @author Administrador
 *
 */
class StatisticSQL {
	
	
	static Logger _log
	public StatisticSQL() {
			
	}
	
	/**
	 * Genera las estadisticas.
	 * 
	 * Tipos de estadisticas:
	 * Para cada opcion de combinaciones
	 * - Word
	 * - Word2
	 * - Word3
	 * - Group
	 * - Group2
	 * - Semantic (incluye los acronimos, guardados en la misma tabla)
	 * Para cada combinacion puede haber para cada tipo de centro 
	 *  - Prov 
	 *  - Instit
	 *  - Org
	 *  - PI
	 *  - PIO
	 *  - PIOD
	 *  - PO
	 * Y dentro de cada combinacion y tipo de centro tenemos la opcion de hacerlo segmentando por provincia o no.
	 * 
	 * Algoritmo:
	 * Crea el sql segun el tipo de estadistica
	 * Lo ejecuta y para cada grupo/s o palabra/s guarda sus estadisticas si superan un umbral (40?)
	 */
	void create(CenterType cenType, StatType wtype, Object ids, String provincia) {
		
		int totalCnt = 0
		def provs = [[]]
		String sql = ""
		sql = selectSql(cenType) + cc2Sql(wtype,ids) + groupSql(cenType, provincia);
		
		Integer siz = getColumnsSize(cenType)

		Database.getInstance().sqlite.eachRow(sql, {
			
			Integer cnt = it[0]
			String a = (it[1] != null)? "'" + it[1]+ "'":"";
			String b = ((siz > 2) && (it[2] != null))? "'" + it[2]+ "'":"";
			String c = ((siz > 3) && (it[3] != null))? "'" + it[3]+ "'":"";
			String d = ((siz > 4) && (it[4] != null))? "'" + it[4]+ "'":"";
			String e = ((siz > 5) && (it[5] != null))? "'" + it[5]+ "'":"";
			
			provs.add([a, b, c, d, e, cnt])
			
			totalCnt += cnt
		})
		
		provs.each { row ->
			if (row.size != 0) {
				
				String insSql = insertSql(cenType,wtype,ids,row,provincia,totalCnt);
		
				Integer cnt = row[5]
				Double per =  (cnt/totalCnt) * 100
				if (per > 40)
					Database.getInstance().sqlite.execute(insSql)
					
				
				
			}
		}
	}
		
   /**
   * Crea la primera parte para la consulta de estadisticas
   * @param centerType
   * @return trozo de sentencia sql con las columnas a obtener
   */
   private String selectSql(CenterType centerType) {
	 switch (centerType) {
		 case CenterType.instit: return "select count(*) as cnt, c.iso, c.instit from center c inner join ";
		 case CenterType.org: return "select count(*) as cnt, c.iso, c.organismo from center c inner join ";
		 case CenterType.pi: return "select count(*) as cnt, c.iso, c.provincia, c.instit from center c inner join ";
		 case CenterType.pio: return "select count(*) as cnt, c.iso, c.provincia, c.instit, c.organismo from center c inner join ";
		 case CenterType.piod: return "select count(*) as cnt, c.iso, c.provincia, c.instit, c.organismo, c.dpto from center c inner join ";
		 case CenterType.po: return "select count(*) as cnt, c.iso, c.provincia, c.organismo from center c inner join ";
		 case CenterType.prov: return "select count(*) as cnt, c.iso, c.provincia from center c inner join ";
		 case CenterType.sect: return "select count(*) as cnt, c.iso, c.sector from center c inner join ";
	 }
	return ""; 
   }
   
   /**
   * Obtiene el numero de columnas que devuelve la consulta segun el tipo de centro que buscamos
   * @param centerType
   * @return numero de columnas
   */
   private Integer getColumnsSize(CenterType centerType) {
	 switch (centerType) {
		 case CenterType.instit: return 3
		 case CenterType.org: return 3
		 case CenterType.pi: return 4
		 case CenterType.pio: return 5
		 case CenterType.piod: return 6
		 case CenterType.po: return 4
		 case CenterType.prov: return 3
		 case CenterType.sect: return 3
	 }
	return 0;
   }
   
   /**
    * Crea la agrupacion para la consulta de estadisticas
    * @param centerType
    * @return trozo de sentencia sql
    */
 private String groupSql(CenterType centerType, String provincia) {
	   
	   String whereProv = ""
	   if (!provincia.isEmpty())
	   	whereProv = "where c.provincia = '${provincia}' "
		   
	 String sql = ""
	 
	 switch (centerType) {
		 case CenterType.instit: sql =  "group by c.iso, c.instit order by cnt desc"; break;
		 case CenterType.org: sql =  "group by c.iso, c.organismo order by cnt desc"; break;
		 case CenterType.pi: sql =  "group by c.iso, c.provincia, c.instit order by cnt desc"; break;
		 case CenterType.pio: sql =  "group by c.iso, c.provincia, c.instit, c.organismo order by cnt desc"; break;
		 case CenterType.piod: sql =  "group by c.iso, c.provincia, c.instit, c.organismo, c.dpto order by cnt desc"; break;
		 case CenterType.po: sql =  "group by c.iso, c.provincia, c.organismo order by cnt desc"; break;
		 case CenterType.prov: sql =  "group by c.iso, c.provincia order by cnt desc"; break;
		 case CenterType.sect: sql =  "group by c.iso, c.sector order by cnt desc"; break;
	 }
	 
	 sql = "on c.idce = cc2.idce " + whereProv + sql
	return sql; 
 } 
 
 private String cc2Sql(StatType type, Object args) {
	 String sql = "";
	 
	 switch (type) {
		 case StatType.group: sql = "(select cc.idce from centers_corps cc " +
		 "	inner join " +
		 "	corpgroup cg on cc.idcorp = cg.idcorp where cg.idgroup = ${args[0]}) as cc2 "; 
		 break;
		case StatType.group2: sql = "(select cc.idce from centers_corps cc " +
"	where cc.idcorp in " +
"(select gw1.idcorp from corpgroup gw1 where gw1.idcorp in (select gw2.idcorp from corpgroup gw2 where gw2.idgroup = ${args[1]}) and gw1.idgroup = ${args[0]}) " +
") as cc2 "
		break;
		case StatType.word: sql = "(select cc.idce from centers_corps cc " +
"	inner join " +
"	(select distinct cg.idcorp from corpgroup cg " +
"		inner join " +
"	groupedword gw on cg.idgroup = gw.idgroup where gw.idword = ${args[0]}) as cg2 " +
"on cc.idcorp = cg2.idcorp) as cc2 "
		break;

		case StatType.word2: sql ="(select cc.idce from centers_corps cc " +
"	where cc.idcorp in " +
"  (select distinct cg.idcorp from corpgroup cg " +
"       where cg.idgroup in " +
"(select gw1.idgroup from groupedword gw1 where gw1.idgroup in (select gw2.idgroup from groupedword gw2 where gw2.idword = ${args[1]}) and gw1.idword = ${args[0]}) " +
") " +
") as cc2 "
		break;
	case StatType.word3: sql = "(select cc.idce from centers_corps cc " +
"	where cc.idcorp in " +
"  (select distinct cg.idcorp from corpgroup cg " +
"       where cg.idgroup in " +
"(select gw1.idgroup from groupedword gw1 where gw1.idgroup in " +
"(select gw2.idgroup from groupedword gw2 where gw2.idgroup in " +
"(select gw3.idgroup from groupedword gw3 where gw3.idword = ${args[2]}) and gw2.idword = ${args[1]}) and gw1.idword = ${args[0]}) " +
") " +
") as cc2 "
		break;
		
		case StatType.sem: sql ="(select cc.idce from centers_corps cc " +
"	inner join " +
"	corpgroup cg on cc.idcorp = cg.idcorp where cg.idgroup in (select sm.idgroup from groupsemantic sm where sm.idgroupsemantic = ${args[0]})) as cc2 "
		break;
		case StatType.wordGroup: sql ="(select cc.idce from centers_corps cc " +
"	where cc.idcorp in " +
"(select gw1.idcorp from corpgroup gw1 where gw1.idcorp in (select distinct cg.idcorp from corpgroup cg " +
"		inner join " +
"	groupedword gw on cg.idgroup = gw.idgroup where gw.idword = ${args[0]} and gw.idgroup <> ${args[1]}) and gw1.idgroup = ${args[1]}) " +
") as cc2 "
				break;
		
	
	 }
	 
	 return sql;
	 
 }
 
 /**
  * Genera la query de insercion
  * @param cenType
  * @param wtype
  * @param ids
  * @param row
  * @param provincia
  * @return
  */
 private String insertSql(CenterType cenType, StatType wtype, Object ids, Object row, String provincia,int totalCnt) {
	 String sql = ""
	
	  String tableProv = ""
	 String valProv = ""
	 if (!provincia.isEmpty()) {
	 	tableProv = "prov_"
		valProv = "'" + provincia + "',";
	 }
   
	String tableCenType = ""
   switch (cenType) {
	   case CenterType.instit: tableCenType =  "Instit"; break;
	   case CenterType.org: tableCenType =  "Org"; break;
	   case CenterType.pi: tableCenType =  "PI"; break;
	   case CenterType.pio: tableCenType =  "PIO"; break;
	   case CenterType.piod: tableCenType =  "PIOD"; break;
	   case CenterType.po: tableCenType =  "PO"; break;
	   case CenterType.prov: tableCenType =  "Prov"; break;
	   case CenterType.sect: tableCenType =  "Sect"; break;
   }
   
   String tableWType = ""
   switch (wtype) {
	   case StatType.group: tableWType = "group" + tableCenType ;  break;
	  case StatType.group2: tableWType = "group" + tableCenType + "2";  break;
	  case StatType.word: tableWType = "word" + tableCenType;  break;
	  case StatType.word2: tableWType = "word" + tableCenType + "2";  break;
	  case StatType.word3: tableWType = "word" + tableCenType + "3";  break;
	  case StatType.sem: tableWType = "sem" + tableCenType;  break;
	  case StatType.wordGroup: tableWType = "wordGroup" + tableCenType;  break;
   }
   
   String values = valProv;
   for (Object i: ids) {
	   values += i + ","
   }
   
   int index = 0; 
   for (Object i: row) {
	   if (index == 5) {
		   Integer cnt = row[5]
		   Double per =  (cnt/totalCnt) * 100
		   values += "${per}, ${totalCnt}, null"
	   } else
	   	   values += (((String)i).isEmpty())? "":i + ","
	   index++;
   }
   
   sql = "INSERT INTO " + tableProv + tableWType + " VALUES (" + values + ")"
   return sql;
   
   
 }
  
}
